package gov.pbc.nn.kjgl.train.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.bstek.bdf2.core.context.ContextHolder;
import com.bstek.bdf2.core.orm.ParseResult;
import com.bstek.dorado.data.entity.EntityUtils;
import com.bstek.dorado.data.provider.Criteria;
import com.bstek.dorado.data.provider.Page;

import gov.pbc.nn.core.bdf.entity.Dept;
import gov.pbc.nn.core.dao.AbstractJdbcDao;
import gov.pbc.nn.core.dao.CommonHibernateDao;
import gov.pbc.nn.core.dao.PbcnnCoreJdbcDao;
import gov.pbc.nn.core.utils.SecurityUtils;
import gov.pbc.nn.kjgl.Constants;
import gov.pbc.nn.kjgl.entity.TrainingPlan;
import gov.pbc.nn.kjgl.entity.TrainingUser;

@Repository
public class TrainRegisterJdbcDao extends AbstractJdbcDao {

	@Resource(name = PbcnnCoreJdbcDao.BEAN_ID)
	private PbcnnCoreJdbcDao pbcnnCoreJdbcDao;

	@Resource(name = CommonHibernateDao.BEAN_ID)
	private CommonHibernateDao hibernateDao;

	/**
	 * 培训参报
	 * 
	 * @param page
	 */
	public void getAll(Page<TrainingPlan> page, Criteria criteria) {
		Dept dept = (Dept) ContextHolder.getLoginUser().getDepts().get(0);
		String sql = "select a.*,b.END_TIME_ from T_TRAINING_PLAN as a inner join T_TRAINING_DEPT as b on a.ID_=b.BUS_KEY_ where b.DEPT_ID_ like :deptId and a.STATE_=15 and a.INVALID_=0 ";
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("deptId", dept.getCDept().getId() + "%");
		ParseResult pr = parseCriteria(criteria, true, null);
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql += " and " + sbSql.toString() + " order by a.CREATE_DATE_ desc ";
			map.putAll(pr.getValueMap());
		} else {
			sql += " order by a.CREATE_DATE_ desc ";
		}

		this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
			public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				int i = 1;
				return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
			}
		}, map);
	}

	// public void getTrainPlanByLoginUser(Page<TrainingPlan> page) {
	// String sql = "select b.* from t_TRAINING_user as a inner join
	// T_TRAINING_PLAN as b on a.BUS_KEY_=b.ID_ where a.USERNAME_=:username and
	// a.INVALID_=0";
	// Map<String, Object> map = new HashMap<String, Object>();
	// map.put("username", ContextHolder.getLoginUserName());
	// this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
	// @Override
	// public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException
	// {
	// // TODO Auto-generated method stub
	// return TrainRegisterJdbcDao.this.mapRow(rs, rowNum);
	// }
	// }, map);
	// }

	public void QueryAll(Page<TrainingPlan> page, Criteria criteria, String type) {

		// type=sbYes/No时表示 我申报的已完成/未完成
		// type=cbYes/No时表示 我参报的已完成/未完成
		// type=jbYes/No时表示 我经办的已完成/未完成
		// type=cyYes/No时表示 我参与的已完成/未完成
		// type=allYes/No时表示 全部 已完成/未完成

		String username = ContextHolder.getLoginUserName();
		Map<String, Object> map = new HashMap<String, Object>();
		String sql = "select a.* from t_training_plan as a ";
		if ("sbYes".equals(type)) {// 查询我申报的培训计划
			sql = sql + " where a.invalid_ =0 and a.create_user_=:username and a.state_=100";
			map.put("username", username);
			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by a.CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}
			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("sbNo".equals(type)) {
			sql = sql + " where a.invalid_ =0 and a.create_user_=:username and a.state_ != 100";
			map.put("username", username);
			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by a.CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}
			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("cbYes".equals(type)) {// 查询我参报的培训计划
			sql = sql
					+ " where a.id_ in (select distinct u.bus_key_ from t_training_user u where u.create_user_=:username) and a.INVALID_=0 and a.state_ = 100 ";
			map.put("username", username);
			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}
			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("cbNo".equals(type)) {
			sql = sql
					+ " where a.id_ in (select distinct u.bus_key_ from t_training_user u where u.create_user_=:username) and a.INVALID_=0 and a.state_ != 100 ";
			map.put("username", username);
			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}
			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("allYes".equals(type)) {// 查询全部培训计划
			if (SecurityUtils.containsRole(ContextHolder.getLoginUserName(), Constants.ROLE_PXGLY)) {
				sql = sql + " where a.invalid_ =0 and a.state_=100";
				ParseResult pr = this.parseCriteria(criteria, true, null);
				if (pr != null) {
					StringBuffer sbSql = pr.getAssemblySql();
					sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
					map.putAll(pr.getValueMap());
				} else {
					sql += " order by a.CREATE_DATE_ desc ";
				}
				this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
					@Override
					public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						int i = 0;
						return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
					}
				}, map);
			}
		} else if ("allNo".equals(type)) {
			if (SecurityUtils.containsRole(ContextHolder.getLoginUserName(), Constants.ROLE_PXGLY)) {
				sql = sql + " where a.invalid_ =0 and a.state_!=100";
				ParseResult pr = this.parseCriteria(criteria, true, null);
				if (pr != null) {
					StringBuffer sbSql = pr.getAssemblySql();
					sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
					map.putAll(pr.getValueMap());
				} else {
					sql += " order by a.CREATE_DATE_ desc ";
				}
				this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
					@Override
					public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
						// TODO Auto-generated method stub
						int i = 0;
						return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
					}
				}, map);
			}
		} else if ("cyYes".equals(type)) {// 查询我参与的培训
			sql = sql + " inner join t_training_user as b on a.id_=b.bus_key_  "
					+ " where  a.invalid_ =0 and b.invalid_ =0 and b.join_=1 and a.state_=100 and  b.username_=:username ";
			map.put("username", username);

			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}

			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("cyNo".equals(type)) {
			sql = sql + " inner join t_training_user as b on a.id_=b.bus_key_  "
					+ " where  a.invalid_ =0 and b.invalid_ =0 and b.join_=1 and a.state_ !=100 and  b.username_=:username ";
			map.put("username", username);

			ParseResult pr = this.parseCriteria(criteria, true, null);
			if (pr != null) {
				StringBuffer sbSql = pr.getAssemblySql();
				sql += " and " + sbSql.toString() + " order by CREATE_DATE_ desc ";
				map.putAll(pr.getValueMap());
			} else {
				sql += " order by a.CREATE_DATE_ desc ";
			}

			this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {
				@Override
				public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
					// TODO Auto-generated method stub
					int i = 0;
					return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
				}
			}, map);
		} else if ("jbYes".equals(type)) {// 查询我经办的
			sql = "select DISTINCT o.BUS_KEY_ from t_uflo_opinion o where o.USERNAME_=? and o.BUS_TABLE_=?";
			List<String> ids = this.pbcnnCoreJdbcDao.getJdbcTemplate().query(sql,
					new Object[] { ContextHolder.getLoginUserName(), "T_TRAINING_PLAN" }, new RowMapper<String>() {

						@Override
						public String mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString("BUS_KEY_");
						}
					});
			if (ids.isEmpty()) {
				return;
			}
			DetachedCriteria dc = this.hibernateDao.buildDetachedCriteria(criteria, TrainingPlan.class);
			dc.add(Restrictions.or(Restrictions.eq("invalid", false), Restrictions.isNull("invalid")));
			dc.add(Restrictions.in("id", ids));
			dc.add(Restrictions.eq("state", 100));
			dc.addOrder(Order.desc("createDate"));
			this.hibernateDao.pagingQuery(page, dc);
		} else if ("jbNo".equals(type)) {
			sql = "select DISTINCT o.BUS_KEY_ from t_uflo_opinion o where o.USERNAME_=? and o.BUS_TABLE_=?";
			List<String> ids = this.pbcnnCoreJdbcDao.getJdbcTemplate().query(sql,
					new Object[] { ContextHolder.getLoginUserName(), "T_TRAINING_PLAN" }, new RowMapper<String>() {

						@Override
						public String mapRow(ResultSet rs, int rowNum) throws SQLException {
							return rs.getString("BUS_KEY_");
						}
					});
			if (ids.isEmpty()) {
				return;
			}
			DetachedCriteria dc = this.hibernateDao.buildDetachedCriteria(criteria, TrainingPlan.class);
			dc.add(Restrictions.or(Restrictions.eq("invalid", false), Restrictions.isNull("invalid")));
			dc.add(Restrictions.in("id", ids));
			dc.add(Restrictions.ne("state", 100));
			dc.addOrder(Order.desc("createDate"));
			this.hibernateDao.pagingQuery(page, dc);
		}
	}

	public TrainingPlan mapRow(ResultSet rs, int rowNum, int i) throws SQLException {
		TrainingPlan m = new TrainingPlan();
		m.setId(rs.getString("ID_"));
		m.setCreateDate(rs.getDate("CREATE_DATE_"));
		m.setUpdateDate(rs.getDate("UPDATE_DATE_"));
		m.setCreateUser(rs.getString("CREATE_USER_"));
		m.setUpdateUser(rs.getString("UPDATE_USER_"));
		m.setInvalid(rs.getBoolean("INVALID_"));
		m.setUpdateUserCname(rs.getString("UPDATE_USER_CNAME_"));
		m.setCreateUserCname(rs.getString("CREATE_USER_CNAME_"));
		m.setProjectId(rs.getString("PROJECT_ID_"));
		m.setProjectName(rs.getString("PROJECT_NAME_"));
		m.setTrainingType(rs.getString("TRAINING_TYPE_"));
		m.setPeriods(rs.getInt("PERIODS_"));
		m.setDays(rs.getInt("DAYS_"));
		m.setPeoples(rs.getInt("PEOPLES_"));
		m.setPxlgzt(rs.getString("PXLGZT_"));
		m.setPxzzxs(rs.getString("PXZZXS_"));
		m.setPxssfs(rs.getString("PXSSFS_"));
		m.setPxdxms(rs.getString("PXDXMS_"));
		m.setZbdw(rs.getString("ZBDW_"));
		m.setCbdw(rs.getString("CBDW_"));
		m.setXbdw(rs.getString("XBDW_"));
		m.setPxdd(rs.getString("PXDD_"));
		m.setNdkssj(rs.getTimestamp("NDKSSJ_"));
		m.setNdjssj(rs.getTimestamp("NDJSSJ_"));
		m.setPxshy(rs.getString("PXSHY_"));
		m.setNdpxxs(rs.getString("NDPXXS_"));
		m.setState(rs.getInt("STATE_"));
		m.setCldsh(rs.getInt("CLD_SH_"));
		m.setApplyDeptId(rs.getString("APPLY_DEPT_ID_"));
		m.setApplyUser(rs.getString("APPLY_USER_"));
		m.setPxshyName(rs.getString("PXSHY_NAME_"));
		m.setCzUsername(rs.getString("CZ_USERNAME_"));
		m.setCzName(rs.getString("CZ_NAME_"));
		if (i == 1) {
			try {
				m = EntityUtils.toEntity(m);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			EntityUtils.setValue(m, "endTime", rs.getTimestamp("END_TIME_"));
		}
		return m;
	}

	/**
	 * 根据 username 得到部门
	 * 
	 * @param username
	 * @return
	 */
	public List<Map<String, Object>> getDept(String username) {
		String sql = "select ud.DEPT_ID_,d.NAME_ from   t_user u  INNER JOIN    bdf2_user_dept ud on ud.USERNAME_=u.USERNAME_ INNER JOIN   t_dept d ON ud.DEPT_ID_=d.ID_ WHERE u.USERNAME_=?";
		List<Map<String, Object>> lists = this.pbcnnCoreJdbcDao.getJdbcTemplate().queryForList(sql,
				new String[] { username });
		List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();
		for (Map<String, Object> map : lists) {
			map.put("deptId", map.get("DEPT_ID_"));
			map.put("deptName", map.get("NAME_"));
			ls.add(map);
		}
		return ls;
	}

	/**
	 * 根据当前登录人 获取TrainingPlan
	 * 
	 * @param page
	 */
	public void getTrainingPlan(Page<TrainingPlan> page, Criteria criteria) {
		String userName = ContextHolder.getLoginUser().getUsername();
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select * from t_training_plan where CREATE_USER_=:username and INVALID_=0 ";
		param.put("username", userName);
		ParseResult pr = parseCriteria(criteria, true, null);
		if (pr != null) {
			StringBuffer sbSql = pr.getAssemblySql();
			sql += " and " + sbSql.toString()
					+ " order by case when STATE_=11 then 100 when STATE_=10 then 99 end desc,CREATE_DATE_ desc ";
			param.putAll(pr.getValueMap());
		} else {
			sql += " order by case when STATE_=11 then 100 when STATE_=10 then 99 end desc,CREATE_DATE_ desc ";
		}
		this.pagingQuery(page, sql, new RowMapper<TrainingPlan>() {

			@Override
			public TrainingPlan mapRow(ResultSet rs, int rowNum) throws SQLException {
				// TODO Auto-generated method stub
				int i = 0;
				return TrainRegisterJdbcDao.this.mapRow(rs, rowNum, i);
			}

		}, param);
	}

}
